use master
go
create database Student
on
(
	name='Student',
	filename='D:\test\Student.mdf',
	size=5,
	maxsize=100,
	filegrowth=5
)
log on
(
name='Student_log',
	filename='D:\test\Student_log.ldf',
	size=5,
	maxsize=100,
	filegrowth=5
)
go
use Student
create table Class
(
ClassID int primary key identity (1,1) not null,
ClassName nvarchar(20) unique not null
)
go

use Student
create table Student
(stuID int primary key identity (1,1) not null,
ClassID int foreign key references Class(ClassID),
StuName nvarchar(20) not null,
StuSex nvarchar(1) check(StuSex in ('','Ů')),
StuBirthday date ,
StuPhone nvarchar(11) unique 
)
go
use Student
create table Course 
(
CourseID int primary key identity(1,1),
CourseName nvarchar(50) unique not null,
CourseCredit int default('1') check(CourseCredit>=1 and CourseCredit<=5)
)

go
use Student
create table Score
(
ScoreID int identity(1,1),
StuID int ,
CourseID int,
Score decimal(5,2) unique not null
)

go
use Student
alter table Student
add StuAddress nvarchar(200) 
go
alter table Score
add constraint FK_Score_ScoreID primary key(ScoreID)

alter table Score
add constraint FK_Score_StuID
foreign key(StuID)references Student(StuID)

alter table Score
add constraint FK_Score_CourseID
foreign key(CourseID)references Course(CourseID)